const express = require("express");
let createDBconnect = require("../../db/createDBconnect");
const router = express.Router();

router.get("/residentsLive/incomeAndCost",(req,res)=>{
    if(req.query?.city == null)
    {
        return res.send({status: 202,message: "查询参数不正确"});
    }
    try{
        const connect = createDBconnect();
        let sqlString = `
        select * from income_country where \`地区\` = '${req.query.city}' union 
        select * from income_towns where \`地区\` = '${req.query.city}' union
        select * from income_village where \`地区\` = '${req.query.city}' union
        select * from cost_country where \`地区\` = '${req.query.city}' union
        select * from cost_towns where \`地区\` = '${req.query.city}' union
        select * from cost_village where \`地区\` = '${req.query.city}'  
        `
        connect.query(sqlString,(err,result)=>{
            if(err){
                console.log(`SQL error: ${err}!`);
                res.status(400)
            }
            else{
                for(let i = 0;i<result.length;i++)
                {
                    delete result[i]['地区'];
                }

                let resultArr = [];
                let years = Object.keys(result[0]);
                for(let i = 0;i<years.length;i++)
                {
                    let resultItem = {
                        year: years[i],
                        income_country: result[0][years[i]],
                        income_towns: result[1][years[i]],
                        income_village: result[2][years[i]],
                        cost_country: result[3][years[i]],
                        cost_towns: result[4][years[i]],
                        cost_village: result[5][years[i]],
                    }
                    resultArr.push(resultItem);
                }
                console.log(resultArr);
                

                res.send({
                    status: 200,
                    data: resultArr,
                });
            }
        })
    }catch(err){
        console.log(err);
    }
});

module.exports = router